package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.Book;
import bean.Cart;
import com.mysql.cj.protocol.Resultset;

public class Cartdao {

    // 向购物车中加入数据项
    public boolean add(String username, Cart cart) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        String sql = "insert into cart (username,isbn,bookname,bookprice,num,booksrc)"
                + " values (?,?,?,?,?,?)";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(username);
        arrayList.add(cart.getIsbn());
        arrayList.add(cart.getBookname());
        arrayList.add(String.valueOf(cart.getBookprice()));
        arrayList.add(String.valueOf(cart.getNum()));
        arrayList.add(cart.getBooksrc());
        try {
            dBbean.executeUpdate(sql, arrayList);
        } catch (Exception e) {
            e.printStackTrace();
            dBbean.close();
            return false;
        }
        dBbean.close();
        return true;
    }

    // 向购物车中更新
    public boolean update(String username, String isbn, int num) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        String sql = "update cart set num=? where username=? and isbn=?";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(String.valueOf(num));
        arrayList.add(username);
        arrayList.add(isbn);

        try {
            dBbean.executeUpdate(sql, arrayList);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            dBbean.close();
            return false;
        }
        dBbean.close();
        return true;
    }


    // 删除该购物项
    public boolean delete(String username, String isbn) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        String sql = "delete from cart where username=? and isbn=?";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(username);
        arrayList.add(isbn);
        try {
            dBbean.executeUpdate(sql, arrayList);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            dBbean.close();
            return false;
        }
        dBbean.close();
        return true;
    }

    // 清空购物车
    public boolean clear(String username) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        String sql = "delete from cart where username=?";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(username);
        try {
            dBbean.executeUpdate(sql, arrayList);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            dBbean.close();
            return false;
        }
        dBbean.close();
        return true;
    }

    public double getSumPrice(String username) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        double price = 0;
        String sql = "select * from cart where username=?";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(username);

        try {
            ResultSet rs = dBbean.executeQuery(sql, arrayList);
            while (rs.next()) {
                price += rs.getDouble(5)*rs.getInt(6);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dBbean.close();
        }
        dBbean.close();
        return price;
    }

    public Cart findById(String username, String isbn) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        Cart cart = null;
        String sql = "select * from cart where username=? and isbn=?";
        ArrayList<Object> arrayList = new ArrayList<>();
        arrayList.add(username);
        arrayList.add(isbn);
        try {
            ResultSet rs = dBbean.executeQuery(sql, arrayList);
            if(rs.next()) {
                cart = new Cart(rs.getInt(1), username, isbn, rs.getString(4), rs.getDouble(5), rs.getInt(6)
                        , rs.getString(7));
            }
        } catch (Exception e) {
            dBbean.close();
            e.printStackTrace();
        }
        dBbean.close();
        return cart;
    }

    public ArrayList getAllItems(String username) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        ArrayList<Cart> list = new ArrayList<Cart>();
        ArrayList<Object> arrayList = new ArrayList<>();

        String sql = "select * from cart where username=?";
        arrayList.add(username);

        try {
            ResultSet rs = dBbean.executeQuery(sql, arrayList);
            while(rs.next()) {
                Cart cart = new Cart(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
                        rs.getDouble(5), rs.getInt(6), rs.getString(7));
                list.add(cart);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dBbean.close();
        }

        dBbean.close();
        return list;
    }

    public ArrayList getList(String username) {
        DBbean dBbean = new DBbean();
        dBbean.getConnection();
        ArrayList<Book> list = new ArrayList<Book>();
        ArrayList<Object> arrayList = new ArrayList<>();

        String sql = "select * from cart where username= ?";
        arrayList.add(username);

        try {
            ResultSet rs = dBbean.executeQuery(sql, arrayList);
            while(rs.next()) {
                Book book = new Book();
                book.setIsbn(rs.getString("isbn"));
                book.setNum(rs.getInt("num"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
            dBbean.close();
        }

        dBbean.close();

        return list;
    }

}
